/*
 * Projet Java / UML -- Trombinoscope - Février / Mars 2012
 * Virginie Messe -- William Huber
 */
package outils;

import java.sql.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import mvc_model.Personne;

/**
 *
 * @author Virginie Messe & William Huber
 */
public class PersonneDAO extends DAO<Personne>{
    
    
    /**
     * 
     * @param obj L'objet à ajouter à la base de données <br/>
     * 
     * requires <br/>
     *      
     * ensures <br/>
     *   // La table en question n'est pas vide
     */
     @Override
    public Personne create(Personne obj) {
         
         Connection connect = this.connect;
         String sql = "INSERT INTO t_personne (nom, prenom, cheminPhoto, adresse, codePostal, ville, telephone, email, accessLevel, login, password, dateNaissance, sexe) VALUES (?, ?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?)";
         String sql2 = "INSERT INTO t_personne_has_t_promotion (t_personne_idPersonne, t_promotion_idPromotion) VALUES (?, ?)";
         int id = 0;
        try {
            PreparedStatement ps = (PreparedStatement) connect.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setString(1, obj.getNom());
            ps.setString(2, obj.getPrenom());
            ps.setString(3, obj.getCheminPhoto());
            ps.setString(4, obj.getAdresse());
            ps.setString(5, obj.getCodePostal());
            ps.setString(6, obj.getVille());
            ps.setString(7, obj.getTelephone());
            ps.setString(8, obj.getEmail());
            ps.setInt(9, obj.getAccessLevel());
            ps.setString(10, obj.getLogin());
            ps.setString(11, obj.getPassword());
            ps.setString(12, obj.getDateNaissance());
            ps.setString(13, obj.getSexe());
            ps.executeUpdate();
            ps.close();
            
            id = findIdPersonne(obj.getNom());
            PreparedStatement ps2 = (PreparedStatement) connect.prepareStatement(sql2);
            ps2.setInt(1, id);
            ps2.setInt(2, obj.getPromotion().getId());
            ps2.executeUpdate();
            ps2.close();
            
            
            
        } catch (SQLException ex) {
            Logger.getLogger(PersonneDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
         
            
	return obj;
}
	
	/**
         * 
         * @param id L'identifiant de l'objet que l'on cherche dans la base <br/>
         * 
         * requires <br/>
         *      
         * ensures <br/>
         *  
         */
	@Override
	public Personne find(int id) {
                PromotionDAO pDAO = new PromotionDAO();
		Personne lang = new Personne();
		try {
            ResultSet result = this.connect
                                   .createStatement(
                                            	ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                                ResultSet.CONCUR_UPDATABLE
                                             ).executeQuery(
                                                "SELECT * FROM t_personne WHERE idPersonne = " + id
                                             );
            ResultSet result2 = this.connect
                                   .createStatement(
                                            	ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                                ResultSet.CONCUR_UPDATABLE
                                             ).executeQuery(
                                                "SELECT * FROM t_personne_has_t_promotion "
                                        + "WHERE t_personne_idPersonne = " + id
                                             );
            
            if(result.first() && result2.first())
            		lang = new Personne(
                                            id,
                                            result.getString("nom"),
                                            result.getString("prenom"),
                                            result.getString("telephone"),
                                            result.getString("adresse"),
                                            result.getString("codePostal"),
                                            result.getString("ville"),
                                            result.getString("email"),
                                            result.getString("cheminPhoto"),
                                            result.getInt("accessLevel"),
                                            result.getString("login"),
                                            result.getString("password"),
                                            result.getString("dateNaissance"),
                                            result.getString("sexe"),
                                            pDAO.find(result2.getInt("t_promotion_idPromotion"))
                                    );
                

            
		    } catch (SQLException e) {
		            e.printStackTrace();
		    }
		   return lang;

	}
	
	@Override
        public Personne update(Personne obj) {
            Connection connect = this.connect;
            String sql = "UPDATE t_personne SET nom = ? ,prenom = ?, telephone = ?, adresse = ?, codePostal = ?, ville = ?, email = ?, cheminPhoto = ?, accessLevel = ?, login = ?, password = ?, dateNaissance = ?, sexe = ? WHERE idPersonne = ?";
            String sql2 = "UPDATE t_personne_has_t_promotion SET t_promotion_id_promotion = ? WHERE t_personne_id_personne = ?";
        try {
            PreparedStatement ps = connect.prepareStatement(sql);
            ps.setString(1, obj.getNom());
            ps.setString(2, obj.getPrenom());
            ps.setString(3, obj.getTelephone());
            ps.setString(4, obj.getAdresse());
            ps.setString(5, obj.getCodePostal());
            ps.setString(6, obj.getVille());
            ps.setString(7, obj.getEmail());
            ps.setString(8, obj.getCheminPhoto());
            ps.setInt(9, obj.getAccessLevel());
            ps.setString(10, obj.getLogin());
            ps.setString(11, obj.getPassword());
            ps.setString(12, obj.getDateNaissance());
            ps.setString(13, obj.getSexe());
            ps.setInt(14, obj.getId());
            ps.executeUpdate();
            ps.close();
            
            PreparedStatement ps2 = connect.prepareStatement(sql2);
            ps2.setInt(1, obj.getPromotion().getId());
            ps2.setInt(2, obj.getId());
            ps2.executeUpdate();
            ps2.close();
        } catch (SQLException ex) {
            Logger.getLogger(PersonneDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
	    
	    
	    return obj;
	}

        @Override
	public void delete(int idPersonne) {
		Connection connect = this.connect;
                String sql = "DELETE FROM t_personne WHERE idPersonne = ?";
                String sql2 ="DELETE FROM t_personne_has_t_promotion WHERE t_personne_idPersonne = ?";
        try {
            PreparedStatement ps = connect.prepareStatement(sql);
            ps.setInt(1, idPersonne);
            ps.executeUpdate();
            ps.close();
            
            PreparedStatement ps2 = connect.prepareStatement(sql2);
            ps2.setInt(1, idPersonne);
            ps2.executeUpdate();
            ps2.close();
        } catch (SQLException ex) {
            Logger.getLogger(PersonneDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
         
	}
        
        @Override
        public int getMax() {
            int res = 0;
            try {
			ResultSet result = this.connect
                                    .createStatement(
                                    		ResultSet.TYPE_SCROLL_INSENSITIVE, 
                                    		ResultSet.CONCUR_UPDATABLE
                                    ).executeQuery(
                                    		"SELECT MAX('idPersonne') as id"
                                    );
			if(result.first()){
				res = result.getInt("id");
			}
	    } catch (SQLException e) {
	            e.printStackTrace();
	    }
	    return res;
	}
        
        public Personne checkAuthentification(String login, String password) {
            Personne p = null;
            int id = 0;
            Connection connect= null;
            Statement statement = null;
            ResultSet res = null;
            try {
                connect = this.connect;
                statement = connect.createStatement();
                res = statement.executeQuery("SELECT idPersonne FROM t_personne where login = '" + login + "' AND password = '" + password + "';");
                if (res.next()){
                    id = res.getInt(1);
                    p = find(id);
                }
                    
            } catch (SQLException e) {
	            e.printStackTrace();
	    }
            return p;
        }
        
        /**
         * 
         * @param idPromo L'identifiant de la promotion dont on veut récupérer
         * les étudiants <br/>
         * 
         * requires <br/>
         *      
         * ensures <br/>
         *  
         */
        public ArrayList<Personne> getPersonnesByPromotion(int idPromo) {
            ArrayList resAL = new ArrayList<Personne>();
            Connection connect_local = null;
            Statement statement = null;
            ResultSet res = null;
            
            Personne perso_une_par_une = new Personne();
            
            try {
                connect_local = this.connect;
                statement = connect_local.createStatement();
                res = statement.executeQuery("SELECT * FROM "
                        + "t_personne_has_t_promotion WHERE t_promotion_idPromotion = "
                        + idPromo
                        + " ;");
                
                // On se place sur la première ligne du ResultSet
                //res.first();
                
                // Parcours du ResultSet
                while (res.next())
                {
                    perso_une_par_une = this.find(res.getInt("t_personne_idPersonne"));
                    resAL.add(perso_une_par_une);
                }

	    } catch (SQLException e) {
	            e.printStackTrace();
	    }
            
            return resAL;
        }
        
               /**
         * 
         * @param idPromo L'identifiant de la promotion dont on veut récupérer
         * les étudiants <br/>
         * 
         * requires <br/>
         *      
         * ensures <br/>
         *  
         */
        public int findIdPersonne(String nom) {
            int result = 0;
            Connection connect_local = null;
            Statement statement = null;
            ResultSet res = null;
            
            try {
                connect_local = this.connect;
                statement = connect_local.createStatement();
                res = statement.executeQuery("SELECT idPersonne FROM t_personne where nom ='" + nom + "';");
                // On se place sur la première ligne du ResultSet
                //res.first();
                
                // Parcours du ResultSet
                if (res.next()){
                    result = res.getInt(1);
                }

	    } catch (SQLException e) {
	            e.printStackTrace();
	    }
            
            return result;
        }
        
        // méthode pour savoir si le login est déja existant dans la base de données
        public boolean isLoginExistant ( String login) {
            boolean result = false;
            
            Connection connect_local = null;
            Statement statement = null;
            ResultSet res = null;
            try {
                connect_local = this.connect;
                statement = connect_local.createStatement();
                res = statement.executeQuery("SELECT idPersonne FROM t_personne where login ='" + login + "';");
                // Parcours du ResultSet
                if (res.next()){
                    result = true;
                }
                } catch (SQLException e) {
	            e.printStackTrace();
	    }
            
            return result;
        }
        
        // Recherche par nom
        public ArrayList<Personne> getResultRecherche(String nom_r) {
            ArrayList resAL = new ArrayList<Personne>();
            Connection connect_local = null;
            Statement statement = null;
            ResultSet res = null;
            
            Personne perso_une_par_une = new Personne();
            
            try {
                connect_local = this.connect;
                statement = connect_local.createStatement();
                res = statement.executeQuery("SELECT * FROM t_personne where nom like '" + nom_r + "%';");
                // On se place sur la première ligne du ResultSet
                //res.first();
                
                // Parcours du ResultSet
                while (res.next()){
                    System.out.println("getResultRecherche" + nom_r);
                    perso_une_par_une = this.find(res.getInt("idPersonne"));
                    System.out.println("getResultRecherche :" + perso_une_par_une.getNom());
                    resAL.add(perso_une_par_une);
                }

	    } catch (SQLException e) {
	            e.printStackTrace();
	    }
            
            return resAL;
        }

}
